﻿using System.Drawing;
using System.Text.Json.Serialization.Metadata;
using DimensionsHelper.Common.Contracts.Excel;
using DimensionsHelper.Common.Excel;
using DimensionsHelper.ExcelAddIn.Core.Properties;

namespace DimensionsHelper.ExcelAddIn.Core.Sql;

/// <summary>
/// SQL脚本生成器
/// </summary>
public class ScriptBuilder : ExcelAddInBase<ScriptSettings, ScriptSettingsTransmission>
{

    protected override LocalSettingService<ScriptSettings> SettingService { get; } = new("SqlScriptBuilder", 
        JsonContext.Default.ScriptSettings);

    protected override JsonTypeInfo<ScriptSettingsTransmission> TransmissionJsonTypeInfo =>
        JsonContext.Default.ScriptSettingsTransmission;

    private RecordTable DataTable { get; } = new();

    private class ErrorValue
    {

        private static readonly ExcelStyleColor WarnColor = Color.Yellow.ToExcelColor();
        private static readonly ExcelStyleColor ErrorColor = Color.Red.ToExcelColor();

        private readonly WeakReference<IExcelWorksheetOperator> _worksheet;
        private readonly string _reason;
        private readonly int _row;
        private readonly int _column;
        private readonly bool _isError;

        private ErrorValue(string reason, IExcelWorksheetOperator worksheet, int row, int column, bool isError)
        {
            _reason = reason;
            _worksheet = new WeakReference<IExcelWorksheetOperator>(worksheet);
            _row = row;
            _column = column;
            _isError = isError;
            WorksheetName = worksheet.Name;
        }


        public static ErrorValue Error(string reason, IExcelWorksheetOperator worksheet, int row, int column)
        {
            return new ErrorValue(reason, worksheet, row, column, true);
        }


        public static ErrorValue Warn(string reason, IExcelWorksheetOperator worksheet, int row, int column)
        {
            return new ErrorValue(reason, worksheet, row, column, false);
        }


        public string WorksheetName { get; }

        
        public void WriteToRange(IExcelRangeOperator range)
        {
            if (!_worksheet.TryGetTarget(out var sheet))
            {
                return;
            }

            var source = sheet.GetRange(_row, _column);
            range.SetHyperlink(source);
            range.Value = _reason;
            range.BackgroundColor = _isError ? ErrorColor : WarnColor;
        }
    }


    private const string IdFieldName = "ID字段名";
    private const string SqlScriptColumnName = "SQL脚本列名";
    private const string FieldName = "字段名";
    private const string MappingName = "映射名";
    private const string ValueLabel = "值标签";
    private const string FieldType = "字段类型";
    private const string FieldValueMapping = "字段值映射";
    private const string ValueLabelDefault = "值标签默认值";
    private const string ExtraLogical = "额外筛选器逻辑";
    private const string CategoricalUpdateType = "分类值更新类型";
    private const string TextId = "字符串ID";
    private const string BuildResultPage = "创建结果页";
    private const string DecimalPlaces = "小数位数";
    private const string Percentage = "添加百分比";
    private const string ResultPageName = "结果页名";

    private const string DecimalsPlaces = "0,1,2,3,4,5,6,7,8,9,10";

    // 记录缺失值
    private readonly List<ErrorValue> _errorValues = [];

    // Setting工作表格式
    // | ID字段名       | SQL脚本列名    | 字段名 | 映射名 | 值标签 | 字段类型 | 字段值映射 | 值标签默认值 | 额外筛选器逻辑 |   分类值更新类型  |
    // |               |               |  ...  |  ...  |  ...  |        |           |           |  AND or OR   | APPEND or RESET |
    // | 字符串ID       | 创建结果页      |  ...  |  ...  |  ...  |
    // | True or False | True or False |  ...  |  ...  |  ...  |
    // |               |  小数位数      |  ...  |  ...  |  ...  |
    // |               |  0 - 10       |  ...  |  ...  |  ...  |
    // |               |  添加百分比     |  ...  |  ...  |  ...  |
    // |               | True or False |  ...  |  ...  |  ...  |
    // |               |  结果页名      |  ...  |  ...  |  ...  |
    // |               |              |  ...  |  ...  |  ...  |

    private void FormatSettingsSheet(IExcelWorksheetOperator sheet)
    {
        // 设置表头
        SetDescriptionCenter(sheet, 1, 1, IdFieldName);
        SetDescriptionCenter(sheet, 1, 2, SqlScriptColumnName);
        SetDescriptionCenter(sheet, 1, 3, FieldName);
        SetDescriptionCenter(sheet, 1, 4, MappingName);
        SetDescriptionCenter(sheet, 1, 5, ValueLabel);
        SetDescriptionCenter(sheet, 1, 6, FieldType);
        SetDescriptionCenter(sheet, 1, 7, FieldValueMapping).Comment = Resources.SqlScriptFieldValueReplacementNote;
        SetDescriptionCenter(sheet, 1, 8, ValueLabelDefault);
        SetDescriptionCenter(sheet, 1, 9, ExtraLogical);
        SetDescriptionCenter(sheet, 1, 10, CategoricalUpdateType);
        // ID字段名
        SetTextCenter(sheet, 2, 1, Settings.IdFieldName);
        // 设置默认SQL脚本列名
        SetTextCenter(sheet, 2, 2, string.IsNullOrWhiteSpace(Settings.ScriptColumnHeader)
            ? Resources.SqlScriptDefaultSqlScriptColumn
            : Settings.ScriptColumnHeader);
        // 设置默认值标签
        SetTextCenter(sheet, 2, 8, Resources.SqlScriptDefaultValueLabel);
        // 设置额外筛选器逻辑
        SetListValidation(sheet, 2, 9,
            $"{LogicKinds.And.ToString().ToUpper()},{LogicKinds.Or.ToString().ToUpper()}",
            Settings.ExtraLogic.ToString().ToUpper());
        // 设置分类值更新类型
        SetListValidation(sheet, 2, 10,
            $"{CategoricalUpdateKinds.Append.ToString().ToUpper()},{CategoricalUpdateKinds.Reset.ToString().ToUpper()}",
            Settings.CategoricalUpdateKind.ToString().ToUpper());
        // 设置文本ID
        SetDescriptionCenter(sheet, 3, 1, TextId);
        SetBooleanValidation(sheet, 4, 1, Settings.IsTextId);
        // 设置创建结果页
        SetDescriptionCenter(sheet, 3, 2, BuildResultPage);
        SetBooleanValidation(sheet, 4, 2, Settings.BuildDataTable);
        // 设置小数位数
        SetDescriptionCenter(sheet, 5, 2, DecimalPlaces);
        SetListValidation(sheet, 6, 2, DecimalsPlaces, Settings.DataTable.DecimalPlaces.ToString());
        // 设置添加百分比
        SetDescriptionCenter(sheet, 7, 2, Percentage);
        SetBooleanValidation(sheet, 8, 2, Settings.DataTable.AddPercentage);
        // 设置结果页名
        SetDescriptionCenter(sheet, 9, 2, ResultPageName);
        SetTextCenter(sheet, 10, 2, string.IsNullOrWhiteSpace(Settings.DataTable.ResultPageName) 
            ? Resources.SqlScriptDefaultSqlTableName 
            : Settings.DataTable.ResultPageName);
        // 第6列设置字段类型检验
        sheet.GetRange(2, 6, 100, 6).SetListValidation("CATEGORICAL,NUMBER,TEXT")
                                    .SetStyle(DefaultCenterStyle);
        sheet.GetRange(2, 3, 100, 5).SetStyle(DefaultStyle);
        // 自适应列宽
        sheet.AutoFitEntireColumns();
    }

    /// <summary>
    /// 创建设置工作表
    /// </summary>
    /// <param name="workbook">工作表所在的工作簿</param>
    public override void BuildSettingSheet(IExcelWorkbookOperator workbook)
    {
        workbook.DeleteWorksheet(Resources.SqlScriptSettingSheetName);
        IExcelWorksheetOperator worksheet = workbook.GetWorksheet(Resources.SqlScriptSettingSheetName);
        // 移动到最后
        worksheet.MoveToEnd();
        // 添加格式
        FormatSettingsSheet(worksheet);
        // 读取字段名
        HashSet<string> fields = ReadFieldNames(workbook);
        if (fields.Count == 0)
        {
            return;
        }
        // 设置字段名列
        worksheet.GetRange(2, 3, fields.Count + 1, 3).Value = fields.ToExcelColumn();
        // 自适应列宽
        worksheet.AutoFitEntireColumns();
    }


    private void LoadSettingsFromSheet(IExcelWorksheetOperator sheet)
    {
        // 读取设置
        if (sheet.GetValues() is not object?[,] values)
        {
            return;
        }
        // 读取ID字段名
        Settings.IdFieldName = ReadAsString(values, 2, 1);
        // 读取SQL脚本列名
        Settings.ScriptColumnHeader = ReadAsString(values, 2, 2) ?? Resources.SqlScriptDefaultSqlScriptColumn;
        // 读取ID字段值是否是字符串类型
        Settings.IsTextId = ReadAsBoolean(values, 4, 1);
        // 读取是否创建结果页
        if (ReadAsBoolean(values, 4, 2))
        {
            Settings.DataTable = new DataTableSettings
            {
                DecimalPlaces = ReadAsInteger(values, 6, 2),
                AddPercentage = ReadAsBoolean(values, 8, 2),
                ResultPageName = ReadAsString(values, 10, 2) ?? Resources.SqlScriptDefaultSqlTableName  
            };
        }
        // 读取额外筛选器逻辑
        Settings.ExtraLogic = ReadAsEnum<LogicKinds>(values, 2, 9);
        // 读取分类值更新类型
        Settings.CategoricalUpdateKind = ReadAsEnum<CategoricalUpdateKinds>(values, 2, 10);
        // 读取值标签默认值
        string? defaultLabel = ReadAsString(values, 2, 8);
        // 读取映射表
        Settings.FieldMap = new Dictionary<string, FieldSettings>(StringComparer.OrdinalIgnoreCase);
        for (int row = 2; row <= values.GetLength(0); row++)
        {
            // 读取字段名
            string? fieldName = ReadAsString(values, row, 3);
            // 跳过字段名是空的行
            if (string.IsNullOrEmpty(fieldName))
            {
                continue;
            }

            if (!Settings.FieldMap.TryGetValue(fieldName!, out var fieldSetting))
            {
                fieldSetting = new FieldSettings()
                {
                    Kind = ReadAsEnum<FieldKinds>(values, row, 6),
                    Label = ReadAsString(values, row, 5) ?? defaultLabel,
                    Replacement = ReadAsString(values, row, 4)
                };

                Settings.FieldMap[fieldName!] = fieldSetting;
            }

            // 读取字段值映射
            string? valueReplacements = ReadAsString(values, row, 7);
            if (string.IsNullOrEmpty(valueReplacements))
            {
                continue;
            }

            string[] pairs = valueReplacements!.Split(';');
            if (pairs.Length == 0)
            {
                continue;
            }

            var map = new Dictionary<string, string>();

            foreach (var pair in pairs)
            {
                string[] replacePair = pair.Split('=');
                // 宽度必须是2
                if (replacePair.Length != 2)
                {
                    continue;
                }
                // 键不能是空
                if (string.IsNullOrWhiteSpace(replacePair[0]))
                {
                    continue;
                }
                // 设置键值对
                map[replacePair[0].Trim()] = replacePair[1].Trim();
            }

            if (map.Count > 0)
            {
                fieldSetting.Replacements = map;
            }
        }
    }


    private void ReadFieldNamesOfSheet(object?[,] values, HashSet<string> targetSet)
    {
        // 读取表头，如果表头没有匹配到字段名列，则跳过
        int lowerBound = values.GetLowerBound(0);
        int fieldColumn = -1;

        for (int i = values.GetLowerBound(1); i <= values.GetUpperBound(1); i++)
        {
            string? text = values[lowerBound, i]?.ToString();
            if (string.IsNullOrEmpty(text) ||
                Settings.Identifiers?.Field?.Contains(text!) != true)
            {
                continue;
            }
            
            fieldColumn = i;
            break;
        }

        if (fieldColumn == -1)
        {
            return;
        }

        // 读取表头下面的所有行
        for (int row = lowerBound + 1; row <= values.GetUpperBound(0); row++)
        {
            string? fieldName = values[row, fieldColumn]?.ToString();
            if (string.IsNullOrEmpty(fieldName))
            {
                continue;
            }
            targetSet.Add(fieldName!);
        }
    }


    private HashSet<string> ReadFieldNames(IExcelWorkbookOperator workbook)
    {
        HashSet<string> fields = [];
        foreach (IExcelWorksheetOperator worksheet in workbook.Worksheets)
        {
            // 跳过SQL_SETTINGS工作表和SQL_Table工作表
            if (worksheet.Name.Equals(Resources.SqlScriptSettingSheetName) || 
                worksheet.Name.Equals(Settings.DataTable.ResultPageName) ||
                worksheet.GetValues() is not object[,] sheetValues)
            {
                continue;
            }

            ReadFieldNamesOfSheet(sheetValues, fields);
        }

        return fields;
    }


    private static string? GetFieldValue(FieldKinds kind, string? value)
    {
        return kind switch
        {
            FieldKinds.Categorical => $"{{{value}}}",
            FieldKinds.Text => $"'{value}'",
            _ => value,
        };
    }


    private string BuildScriptOfField(string field, FieldKinds kind, string? value, bool removeValue)
    {
        if (kind != FieldKinds.Categorical || Settings.CategoricalUpdateKind == CategoricalUpdateKinds.Reset)
        {
            return removeValue ? $"{field} = NULL" : $"{field} = {GetFieldValue(kind, value)}";
        }

        return $"{field} = {(removeValue ? "DIFFERENCE" : "UNION")}({field}, {GetFieldValue(kind, value)})";
    }


    private static void ReplaceFieldValue(FieldSettings field, ref string? value)
    {
        if (string.IsNullOrEmpty(value))
        {
            return;
        }

        if (field.Replacements?.TryGetValue(value!, out string rep) == true)
        {
            value = rep;
        }

        if (!string.IsNullOrEmpty(value) && field.Kind == FieldKinds.Categorical)
        {
            value = field.Label + value;
        }
    }


    private void ReplaceFieldValue(ref string fieldName, ref FieldKinds kind, ref string? value, ref string? value2)
    {
        if (Settings.FieldMap?.TryGetValue(fieldName, out var fieldSettings) != true)
        {
            return;
        }

        kind = fieldSettings!.Kind;
        if (!string.IsNullOrEmpty(fieldSettings.Replacement))
        {
            fieldName = fieldSettings.Replacement!;
        }

        ReplaceFieldValue(fieldSettings, ref value);
        ReplaceFieldValue(fieldSettings, ref value2);
    }


    private void BuildScriptOfSheet(IExcelWorksheetOperator worksheet)
    {
        if (worksheet.GetValues() is not object?[,] cells)
        {
            return;
        }

        // Bounds
        int rowLowerBound = cells.GetLowerBound(0);
        int rowUpperBound = cells.GetUpperBound(0);
        int colLowerBound = cells.GetLowerBound(1);
        int colUpperBound = cells.GetUpperBound(1);
        int rowLength = rowUpperBound - rowLowerBound + 1;
        // Scripts
        string[,] scripts = new string[rowLength, 1];
        scripts[rowLength - 1, 0] = string.IsNullOrWhiteSpace(Settings.ScriptColumnHeader)
            ? Resources.SqlScriptDefaultSqlScriptColumn : Settings.ScriptColumnHeader;
        // 匹配关键列
        int idCol = -1, fieldCol = -1, valueCol = -1, deleteCol = -1;
        int scriptCol = colUpperBound + 1 + (rowLowerBound == 0 ? 1 : 0);
        var extras = new List<int>();
        var extraHeaders = new List<string>();
        // 确保列号从1开始
        int c = 0;
        for (int i = colLowerBound; i <= colUpperBound; i++)
        {
            c++;
            string? cell = ReadAsString(cells, rowLowerBound, i);
            if (string.IsNullOrEmpty(cell))
            {
                continue;
            }

            if (Settings.Identifiers?.Id?.Contains(cell!) == true)
            {
                idCol = c;
            }
            else if (Settings.Identifiers?.Field?.Contains(cell!) == true)
            {
                fieldCol = c;
            }
            else if (Settings.Identifiers?.Value?.Contains(cell!) == true)
            {
                valueCol = c;
            }
            else if (Settings.Identifiers?.ValueToRemove?.Contains(cell!) == true)
            {
                deleteCol = c;
            }
            else if (Settings.Identifiers?.Extra?.Contains(cell!) == true)
            {
                extras.Add(c);
                extraHeaders.Add(cell!);
            }
            else if (cell == Settings.ScriptColumnHeader)
            {
                scriptCol = c;
            }
        }

        // ID列和字段名列不能为空，值列和移除值列不能同时为空
        if (idCol == -1 || fieldCol == -1 || valueCol == -1 && deleteCol == -1)
        {
            return;
        }

        // 如果已有SQL脚本列，则删除
        if (scriptCol > 0)
        {
            worksheet.DeleteColumn(scriptCol);
            ShiftColumns(ref idCol, scriptCol);
            ShiftColumns(ref fieldCol, scriptCol);
            ShiftColumns(ref valueCol, scriptCol);
            ShiftColumns(ref deleteCol, scriptCol);

            for (int i = 0; i < extras.Count; i++)
            {
                if (extras[i] > scriptCol)
                {
                    extras[i]--;
                }
            }
        }

        // 读取表头下面的所有行
        int r = 0;
        for (int row = rowLowerBound + 1; row <= rowUpperBound; row++)
        {
            string? id = ReadAsString(cells, row, idCol);
            string? field = ReadAsString(cells, row, fieldCol);

            r++;

            if (string.IsNullOrEmpty(id))
            {
                _errorValues.Add(ErrorValue.Error("ID缺失", worksheet, row, idCol));
                continue;
            }

            if (string.IsNullOrEmpty(field))
            {
                _errorValues.Add(ErrorValue.Error("字段缺失", worksheet, row, fieldCol));
                continue;
            }

            string recordId = id!;
            string recordLeft = Settings.IdFieldName ?? ReadAsString(cells, rowLowerBound, idCol) ?? "";

            if (Settings.IsTextId)
            {
                recordId = $"'{recordId}'";
                recordLeft = $"Trim({recordLeft})";
            }

            string query = $" WHERE {recordLeft} = {recordId}";
            string?[] extraValues = [.. extras.Select(e => ReadAsString(cells, row, e))];
            string?[] extraQueries = [.. extraHeaders.Select((h, i) =>
            {
                string? val = extraValues[i];
                if (string.IsNullOrEmpty(val))
                {
                    return null;
                }

                FieldKinds kind = FieldKinds.Categorical;
                string ext = h;
                if (Settings.ExtraMap?.TryGetValue(h, out string? mapped) == true)
                {
                    ext = mapped;
                }

                string? removed = null;
                ReplaceFieldValue(ref ext, ref kind, ref val, ref removed);
                CheckFieldValue(kind, val!, row, i);
                return $"{ext} {(kind == FieldKinds.Categorical ? "*" : "=")} {GetFieldValue(kind, val)}";
            }).Where(e => !string.IsNullOrEmpty(e))];

            if (extraValues.Length > 0)
            {
                recordId = $"{recordId}|{string.Join("|", extraValues.Select(e => string.IsNullOrWhiteSpace(e) ? "NULL" : e))}";
            }

            if (extraQueries.Length > 0)
            {
                query = Settings.ExtraLogic == LogicKinds.And
                    ? $"{query} AND {string.Join(" AND ", extraQueries)}"
                    : $"{query} AND ({string.Join(" OR ", extraQueries)})";
            }

            string? appendVal = ReadAsString(cells, row, valueCol);
            string? removeVal = ReadAsString(cells, row, deleteCol);

            if (string.IsNullOrEmpty(appendVal) && string.IsNullOrEmpty(removeVal))
            {
                _errorValues.Add(ErrorValue.Error("值缺失", worksheet, row, valueCol));
                continue;
            }

            FieldKinds fieldKind = FieldKinds.Categorical;
            ReplaceFieldValue(ref field!, ref fieldKind, ref appendVal, ref removeVal);
            CheckFieldValue(fieldKind, appendVal, row, valueCol);
            CheckFieldValue(fieldKind, appendVal, row, deleteCol);
            string sql = "";
            if (!string.IsNullOrEmpty(removeVal))
            {
                sql = $"UPDATE vdata SET {BuildScriptOfField(field, fieldKind, removeVal, true)}{query}";
                DataTable.Remove(field, removeVal!, id!);
            }

            if (!string.IsNullOrEmpty(appendVal))
            {
                if (!string.IsNullOrEmpty(sql))
                {
                    sql += Environment.NewLine;
                }

                sql += $"UPDATE vdata SET {BuildScriptOfField(field, fieldKind, appendVal, false)}{query}";
                DataTable.Add(field, appendVal!, id!);
            }

            scripts[r, 0] = sql;
        }

        // 设置SQL脚本列
        worksheet.GetRange(rowLowerBound == 0 ? 1 : rowLowerBound, scriptCol, rowUpperBound + (rowLowerBound == 0 ? 1 : 0), scriptCol).Value = scripts;
        SetDescriptionCenter(worksheet, 1, scriptCol, string.IsNullOrWhiteSpace(Settings.ScriptColumnHeader)
            ? Resources.SqlScriptDefaultSqlScriptColumn
            : Settings.ScriptColumnHeader!);

        worksheet.AutoFitEntireColumns();

        void ShiftColumns(ref int column, int deleted)
        {
            if (column > deleted)
            {
                column--;
            }
        }

        void CheckFieldValue(FieldKinds kind, string? value, int row, int column)
        {
            if (kind != FieldKinds.Number || string.IsNullOrEmpty(value) || column == -1 || double.TryParse(value, out _))
            {
                return;
            }

            _errorValues.Add(ErrorValue.Warn($"值'{value}'不是数字", worksheet, row, column));
        }
    }


    public override void Run(IExcelWorkbookOperator workbook)
    {
        if (workbook.ContainsWorksheet(Resources.SqlScriptSettingSheetName))
        {
            LoadSettingsFromSheet(workbook.GetWorksheet(Resources.SqlScriptSettingSheetName));
        }
        // 清空已有的统计结果
        DataTable.Clear();
        // 遍历所有工作表
        foreach (IExcelWorksheetOperator worksheet in workbook.Worksheets)
        {
            string worksheetName = worksheet.Name;
            // 跳过SQL_SETTINGS工作表和SQL_Table工作表
            if (worksheetName.Equals(Resources.SqlScriptSettingSheetName) ||
                worksheetName.Equals(Settings.DataTable.ResultPageName) ||
                worksheetName.Equals(Resources.SqlScriptErrorValueSheetName))
            {
                continue;
            }
            // 创建SQL脚本
            BuildScriptOfSheet(worksheet);
        }
        // 输出统计数据表
        if (Settings.BuildDataTable && !string.IsNullOrEmpty(Settings.DataTable.ResultPageName) && !DataTable.IsEmpty)
        {
            // 删除旧的结果页
            workbook.DeleteWorksheet(Settings.DataTable.ResultPageName);
            IExcelWorksheetOperator dataTableSheet = workbook.GetWorksheet(Settings.DataTable.ResultPageName);
            DataTable.WriteToExcel(dataTableSheet, decimals: Settings.DataTable.DecimalPlaces,
                addPercentage: Settings.DataTable.AddPercentage);
            dataTableSheet.AutoFitEntireColumns();
            dataTableSheet.MoveToEnd();
        }
        // 输出缺失值表格
        if (_errorValues.Count > 0)
        {
            // 重建工作表
            workbook.DeleteWorksheet(Resources.SqlScriptErrorValueSheetName);
            IExcelWorksheetOperator missingValueSheet = workbook.GetWorksheet(Resources.SqlScriptErrorValueSheetName);
            // 根据表格分组
            var group = _errorValues.GroupBy(e => e.WorksheetName);
            // 每一组占用一列
            int col = 1;
            foreach (var g in group)
            {
                // 第一行标记工作表名称
                SetDescriptionCenter(workbook.GetWorksheet(Resources.SqlScriptErrorValueSheetName), 1, col, g.Key);
                int row = 2;
                foreach (var value in g)
                {
                    value.WriteToRange(missingValueSheet.GetRange(row, col));
                    row++;
                }
                col++;
            }
            missingValueSheet.AutoFitEntireColumns();
            missingValueSheet.TabColor = Color.Red.ToExcelColor();
            missingValueSheet.MoveToEnd();
            _errorValues.Clear();
        }
    }
}